package com.dy.yunying.biz.dao.znfx;

import com.dy.yunying.api.entity.znfx.ChartDataDetail;
import com.dy.yunying.api.req.znfx.ZnfxReq;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * 计划用户分析
 * @description:
 * @author: leisw
 * @time: 2023/3/22 15:25
 */
@Slf4j
@Component
public class PlanUserDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	/**
	 * 查询点击时间分布
	 * @param znfxReq
	 * @return
	 */
	public List<ChartDataDetail> selectClicktimeData(ZnfxReq znfxReq) {
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adAccount = znfxReq.getAdAccount();
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("SELECT\n" +
				"toHour(toDateTime(reg.click_time/1000)) as name,count(DISTINCT reg.kid)  as value\n" +
				"FROM ").append("\n");
		collectSql.append("  ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		collectSql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> ''").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and reg.reg_day >= " + startTime + " and reg.reg_day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			collectSql.append(" AND reg.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			collectSql.append(" AND reg.ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		collectSql.append(" AND reg.ad_id IN  ( ").append(getAdIdFilterSql(znfxReq)).append(" )").append("\n");
		collectSql.append("group by toHour(toDateTime(reg.click_time/1000))\n" +
				"order by toHour(toDateTime(reg.click_time/1000)) asc\n" +
				"WITH FILL -- 填充\n" +
				"FROM 0 TO 24 -- 补全没有数据的时间段\n" +
				"STEP 1");
		log.info("查询点击时间分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();

		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询点击时间分布耗时: {}ms", end - start);
		return  list;
	}


	/**
	 * 查询在线时长分布
	 * @param znfxReq
	 * @return
	 */
	public List<ChartDataDetail> selectOnlineTimeData(ZnfxReq znfxReq) {
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adAccount = znfxReq.getAdAccount();
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"   CASE WHEN online_time >0 and online_time<=5 THEN '0~5分钟'\n" +
				"	WHEN online_time >5 and online_time<=20 THEN '5~20分钟'\n" +
				"	WHEN online_time >20 and online_time<=60 THEN '20~60分钟'\n" +
				"	WHEN online_time >60 and online_time<=120 THEN '1小时~2小时'\n" +
				"	WHEN online_time >120 THEN '2小时以上'\n" +
				"	ELSE '0' END as name,\n" +
				"	count(DISTINCT kid) as value \n" +
				"	FROM  (	").append("\n");
		collectSql.append(" SELECT\n" +
				"	reg.reg_day reg_day,reg.uuid  as uuid,reg .game_sub as game_sub, reg.kid as kid,dot.online_time as online_time \n" +
				"FROM ").append("\n");
		collectSql.append("  ").append(yunYingProperties.getNinetydeviceregtable() ).append(" reg\n");
		collectSql.append("  LEFT JOIN \n" +
				"( SELECT online_day, uuid, game_sub, online_time " +
				"FROM ").append("\n");
		collectSql.append(yunYingProperties.getDeviceOnlineTimetable()).append("  ").append("\n");
		collectSql.append(" WHERE  1=1 ").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and online_day >= " + startTime + " and online_day <=" + endTime + " \n");
		}
		collectSql.append("  ) dot on reg.reg_day = dot.online_day and reg.uuid =dot.uuid and reg.game_sub = dot.game_sub");
		collectSql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> ''").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and reg.reg_day >= " + startTime + " and reg.reg_day <= " + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			collectSql.append(" AND reg.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			collectSql.append(" AND reg.ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		collectSql.append(" AND reg.ad_id IN  ( ").append(getAdIdFilterSql(znfxReq)).append(" )").append(" )").append("\n");
		collectSql.append("group by " +
				"	CASE WHEN online_time >0 and online_time<=5 THEN '0~5分钟'\n" +
				"	WHEN online_time >5 and online_time<=20 THEN '5~20分钟'\n" +
				"	WHEN online_time >20 and online_time<=60 THEN '20~60分钟'\n" +
				"	WHEN online_time >60 and online_time<=120 THEN '1小时~2小时'\n" +
				"	WHEN online_time >120 THEN '2小时以上'\n" +
				"	ELSE '0' END");
		log.info("查询在线时长分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();

		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询在线时长分布耗时: {}ms", end - start);
		return  list;
	}


	/**
	 * 查询系统分布
	 * @param znfxReq
	 * @return
	 */
	public List<ChartDataDetail> selectOsData(ZnfxReq znfxReq) {
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adAccount = znfxReq.getAdAccount();
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"   CASE WHEN reg.os = 0 THEN concat('Android', reg.os_version) \n" +
				"	WHEN reg.os = 1 THEN concat('IOS', reg.os_version)\n" +
				"	WHEN reg.os = 3 THEN '其他'\n" +
				"	WHEN reg.os = 4 THEN reg.os_version\n" +
				"	WHEN reg.os = 5 THEN reg.os_version\n" +
				"	ELSE reg.os_version END as name,\n" +
				"   count(DISTINCT reg.kid) as value \n" +
				"FROM ").append("\n");
		collectSql.append("  ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		collectSql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> ''").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and reg.reg_day >= " + startTime + " and reg.reg_day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			collectSql.append(" AND reg.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			collectSql.append(" AND reg.ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		collectSql.append(" AND reg.ad_id IN  ( ").append(getAdIdFilterSql(znfxReq)).append(" )").append("\n");
		collectSql.append("group by " +
				"	CASE WHEN reg.os = 0 THEN concat('Android', reg.os_version) \n" +
				"	WHEN reg.os = 1 THEN concat('IOS', reg.os_version)\n" +
				"	WHEN reg.os = 3 THEN '其他'\n" +
				"	WHEN reg.os = 4 THEN reg.os_version\n" +
				"	WHEN reg.os = 5 THEN reg.os_version\n" +
				"	ELSE reg.os_version END");
		log.info("查询系统分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();

		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询系统分布耗时: {}ms", end - start);
		return  list;
	}


	/**
	 * 查询机型分布sql
	 * @param znfxReq
	 * @return
	 */
	public List<ChartDataDetail> selectModeData(ZnfxReq znfxReq) {
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adAccount = znfxReq.getAdAccount();
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"   device_brand as name,\n" +
				"   count(DISTINCT reg.kid) as value \n" +
				"FROM ").append("\n");
		collectSql.append("  ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		collectSql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> '' and device_brand <> '' and device_brand is not null").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and reg.reg_day >= " + startTime + " and reg.reg_day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			collectSql.append(" AND reg.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			collectSql.append(" AND reg.ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		collectSql.append(" AND reg.ad_id IN  ( ").append(getAdIdFilterSql(znfxReq)).append(" )").append("\n");
		collectSql.append("group by device_brand ");
		log.info("查询机型分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();

		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询机型分布耗时: {}ms", end - start);
		return  list;
	}


	/**
	 *
	 * @param znfxReq
	 * @return
	 */
	private String getAdIdFilterSql(ZnfxReq znfxReq){
		//查询参数
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adId = znfxReq.getAdId();
		String adAccount = znfxReq.getAdAccount();
		String firstRoiMin = znfxReq.getFirstRoiMin();
		String firstRoiMax = znfxReq.getFirstRoiMax();
		String threeRoiMin = znfxReq.getThreeRoiMin();
		String threeRoiMax = znfxReq.getThreeRoiMax();
		String sevenRoiMin = znfxReq.getSevenRoiMin();
		String sevenRoiMax = znfxReq.getSevenRoiMax();
		String fifteenRoiMin = znfxReq.getFifteenRoiMin();
		String fifteenRoiMax = znfxReq.getFifteenRoiMax();
		String thirtyRoiMin = znfxReq.getThirtyRoiMin();
		String thirtyRoiMax = znfxReq.getThirtyRoiMax();
		String regCostMin = znfxReq.getRegCostMin();
		String regCostMax = znfxReq.getRegCostMax();
		String firstPayCostMin = znfxReq.getFirstPayCostMin();
		String firstPayCostMax = znfxReq.getFirstPayCostMax();
		String totalPayCostMin = znfxReq.getTotalPayCostMin();
		String totalPayCostMax = znfxReq.getTotalPayCostMax();

		StringBuilder sql = new StringBuilder();
		sql.append("select \n" +
				"	ad_id\n" +
				"	from\n" +
				"	(	").append("\n");
		sql.append("SELECT \n" +
				"  ad_id,\n" +
				"  round(if(cost > 0, divide(toFloat64(day1DevicesharFee) * 100.00, cost), 0), 2) roi1, -- 1日ROI\n" +
				"  round(if(cost > 0, divide(toFloat64(day3DevicesharFee) * 100.00, cost), 0), 2) roi3, -- 3日ROI\n" +
				"  round(if(cost > 0, divide(toFloat64(day7DevicesharFee) * 100.00, cost), 0), 2) roi7, -- 7日ROI\n" +
				"  round(if(cost > 0, divide(toFloat64(day15DevicesharFee) * 100.00, cost), 0), 2) roi15, -- 15日ROI\n" +
				"  round(if(cost > 0, divide(toFloat64(day30DevicesharFee) * 100.00, cost), 0), 2) roi30, -- 30日ROI\n" +
				"  round(IF(toInt64(usrnameNums) > 0, divide(toFloat64(cost), usrnameNums), 0), 2) AS regCost, -- 注册成本\n" +
				"  round(IF(toInt64(usrpaynameNums) > 0, divide(toFloat64(cost), usrpaynameNums), 0), 2) AS firstPayCost,  -- 首日付费成本\n" +
				"  round(IF(toInt64(totalPayfeeNums) > 0, divide(toFloat64(cost), totalPayfeeNums), 0), 2) AS totalPayCost -- 累计付费成本\n");
		sql.append("FROM ( ").append("\n");
		sql.append("select").append("\n");
		sql.append("ad_id, \n" +
				" COALESCE(COUNT(IF(reg.latest_username != '', reg.latest_username, NULL)), 0) usrnameNums,-- 新增设备注册数\n" +
				" COALESCE(SUM(IF(reg.fee_1 > 0 OR reg.givemoney_1 > 0, 1, 0)), 0) usrpaynameNums, --新增设备付费数\n" +
				" COALESCE(SUM(IF(reg.fee_total > 0 OR reg.givemoney_total > 0, 1, 0)), 0) totalPayfeeNums, --累计充值人数\n" +
				" COALESCE(SUM(reg.fee_1 * sharing), 0) day1DevicesharFee, --新增充值实付金额（分成后）\n" +
				" COALESCE(SUM(reg.fee_3 * sharing), 0) day3DevicesharFee, --3日充值实付金额（分成后）\n" +
				" COALESCE(SUM(reg.fee_7 * sharing), 0) day7DevicesharFee, --7日充值实付金额（分成后）\n" +
				" COALESCE(SUM(reg.fee_15 * sharing), 0) day15DevicesharFee, --15日充值实付金额（分成后）\n" +
				" COALESCE(SUM(reg.fee_30 * sharing), 0) day30DevicesharFee --30日充值实付金额（分成后）").append("\n");
		sql.append("FROM ( ").append("\n");
		sql.append("SELECT\n" +
				" 		game_main pgid,ad_id,latest_username, is_2_retention, fee_1, givemoney_1,\n" +
				" 		fee_3, fee_7, fee_15, fee_30, fee_total, givemoney_total\n");
		sql.append("  FROM\n");
		sql.append("  ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		sql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> ''").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			sql.append(" and reg_day >= " + startTime + " and reg_day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			sql.append(" AND chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adId)) {
			sql.append(" AND ad_id IN ('").append(adId.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			sql.append(" AND ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		sql.append(" ) reg").append("\n");
		sql.append("LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM  ").append("\n");
		sql.append("		").append(yunYingProperties.getParentGameTable()).append(" 	pg_tmp) pg ON reg.pgid = pg.id").append("\n");
		sql.append("GROUP BY\n" +
				"   ad_id\n" +
				"  	) a	").append("\n");
		sql.append("FULL JOIN (\n" +
				"   SELECT").append("\n");
		sql.append("ad_id,\n" +
				"   COUNT(DISTINCT ad_id) adidNums, -- 计划数\n" +
				"   toFloat64(COALESCE(SUM(rudeCost), 0)) rudeCost, --原始消耗\n" +
				"   toFloat64(COALESCE(SUM(cost), 0)) cost, --返点后消耗\n" +
				"   COALESCE(SUM(shownums), 0) showNums, --展示数\n" +
				"   COALESCE(SUM(clicknums), 0) clickNums --点击数\n");
		sql.append("FROM\n" +
				"  (\n" +
				"  SELECT\n" +
				"  ad_show shownums, click clicknums, ad_id, COALESCE(rude_cost, 0) rudeCost, COALESCE(cost,0) cost\n" +
				"  FROM").append("\n");
		sql.append(yunYingProperties.getAdidrebatetable()).append("  ard").append("\n");
		sql.append("WHERE 1=1").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			sql.append(" and day >= " + startTime + " and day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(adId)) {
			sql.append(" AND ad_id IN ('").append(adId.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			sql.append(" AND ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		sql.append(" ) ard").append("\n");
		sql.append("LEFT JOIN (SELECT  chl_main,ad_id FROM ").append("\n");
		sql.append(yunYingProperties.getAdptypetable()).append(" ) ap ON ard.ad_id = ap.ad_id").append("\n");
		sql.append("WHERE 1=1 ").append("\n");
		if (StringUtils.isNotBlank(parentChlArr)) {
			sql.append(" AND ap.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		sql.append("GROUP BY  ad_id").append("\n");
		sql.append(") b USING (ad_id)").append("\n");
		sql.append(" WHERE ad_id <> '' ").append("\n");
		if(StringUtils.isNotBlank(firstRoiMin)){
			sql.append(" and roi1 >= " + firstRoiMin + " \n");
		}

		if(StringUtils.isNotBlank(firstRoiMax)){
			sql.append(" and roi1 <= " + firstRoiMax + " \n");
		}

		if(StringUtils.isNotBlank(threeRoiMin)){
			sql.append(" and roi3 >= " + threeRoiMin + " \n");
		}

		if(StringUtils.isNotBlank(threeRoiMax)){
			sql.append(" and roi3 <= " + threeRoiMax + " \n");
		}

		if(StringUtils.isNotBlank(sevenRoiMin)){
			sql.append(" and roi7 >= " + sevenRoiMin + " \n");
		}

		if(StringUtils.isNotBlank(sevenRoiMax)){
			sql.append(" and roi7 <= " + sevenRoiMax + " \n");
		}

		if(StringUtils.isNotBlank(fifteenRoiMin)){
			sql.append(" and roi15 >= " + fifteenRoiMin + " \n");
		}

		if(StringUtils.isNotBlank(fifteenRoiMax)){
			sql.append(" and roi15 <= " + fifteenRoiMax + " \n");
		}

		if(StringUtils.isNotBlank(thirtyRoiMin)){
			sql.append(" and roi30 >= " + thirtyRoiMin + " \n");
		}

		if(StringUtils.isNotBlank(thirtyRoiMax)){
			sql.append(" and roi30 <= " + thirtyRoiMax + " \n");
		}

		if(StringUtils.isNotBlank(regCostMin)){
			sql.append(" and regCost >= " + regCostMin + " \n");
		}

		if(StringUtils.isNotBlank(regCostMax)){
			sql.append(" and regCost <= " + regCostMax + " \n");
		}

		if(StringUtils.isNotBlank(firstPayCostMin)){
			sql.append(" and firstPayCost >= " + firstPayCostMin + " \n");
		}

		if(StringUtils.isNotBlank(firstPayCostMax)){
			sql.append(" and firstPayCost <= " + firstPayCostMax + " \n");
		}

		if(StringUtils.isNotBlank(totalPayCostMin)){
			sql.append(" and totalPayCost >= " + totalPayCostMin + " \n");
		}

		if(StringUtils.isNotBlank(totalPayCostMax)){
			sql.append(" and totalPayCost <= " + totalPayCostMax + " \n");
		}
		sql.append(" )");
		return sql.toString();
	}
	/**
	 * 计划用户分析-地域分布
	 * @param znfxReq
	 * @return
	 */
	public List<ChartDataDetail> selectAreaData(ZnfxReq znfxReq) {
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adAccount = znfxReq.getAdAccount();
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"   IF(vip.dx_province = '' or vip.dx_province is null, IF(vip.dx_country ='' or vip.dx_country is null, '未知', vip.dx_country), vip.dx_province) as name,\n" +
				"   count(1) as value \n" +
				"FROM ").append("\n");
		collectSql.append("  ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		collectSql.append(" left join " + yunYingProperties.getIpTable() + " vip \n" +
				" on replace(arrayStringConcat(arrayPopBack(splitByString('.', splitByString(',', reg.ip)[1])), '.'), ' ', '' ) = vip.dx_short_ip \n");
		collectSql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> ''").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and reg.reg_day >= " + startTime + " and reg.reg_day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			collectSql.append(" AND reg.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			collectSql.append(" AND reg.ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		collectSql.append(" AND reg.ad_id IN  ( ").append(getAdIdFilterSql(znfxReq)).append(" )").append("\n");
		collectSql.append("group by name ");
		collectSql.append("order by name asc ");
		log.info("查询地域分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();

		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询地域分布耗时: {}ms", end - start);
		return  list;
	}

	public List<ChartDataDetail> selectAgeData(ZnfxReq znfxReq) {
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adAccount = znfxReq.getAdAccount();
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"    IF(tmp_age=-1, '未实名', toString(tmp_age)) as name, count(distinct latest_username) as value" +
				" 	from ( \n" +
				"      select " +
				"		reg.latest_username,\n" +
				"				IF(LENGTH(realname.id_num) = 18, CAST(SUBSTRING(realname.id_num, 7, 4) as Int), toYear(today())+1) as brith_year,\n" +
				"            toYear(today()) - brith_year as age,\n" +
				"            IF(age=-1, -1, (case when age<18 then 17\n" +
				"            when age>=18 and age<24 then 18\n" +
				"            when age>=24 and age<31 then 24\n" +
				"            when age>=31 and age<41 then 31\n" +
				"            when age>=41 and age<50 then 41\n" +
				"            else 50 end)) AS tmp_age \n" +
				"      FROM ").append("\n");
		collectSql.append("  ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		collectSql.append(" left join " + yunYingProperties.getWanUserRealnameView() + " realname \n" +
				" on toUInt64(reg.game_main)  = realname.pgid \n" +
				" and realname.ai = reg.latest_username \n" +
				" and realname.status =0 ");
		collectSql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> ''").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and reg.reg_day >= " + startTime + " and reg.reg_day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			collectSql.append(" AND reg.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			collectSql.append(" AND reg.ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		collectSql.append(" AND reg.ad_id IN  ( ").append(getAdIdFilterSql(znfxReq)).append(" )").append("\n");
		collectSql.append(" )\n" +
				"group by tmp_age \n" +
				"order by tmp_age asc");
		log.info("查询年龄分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();

		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询年龄分布耗时: {}ms", end - start);
		return  list;
	}

	public List<ChartDataDetail> selectSexData(ZnfxReq znfxReq) {
		String startTime = znfxReq.getStartTime();
		String endTime = znfxReq.getEndTime();
		String parentChlArr = znfxReq.getParentchlArr();
		String adAccount = znfxReq.getAdAccount();
		//组装查询sql
		final StringBuilder collectSql = new StringBuilder();
		collectSql.append("select \n" +
				"    sex as name, count(distinct latest_username) as value" +
				" 	from ( \n" +
				"      select " +
				"				reg.latest_username,\n" +
				"        IF(LENGTH(realname.id_num) = 18, if(toInt32OrZero(substring(realname.id_num, 17, 1 ))% 2 = 1, '男', '女'), '未实名') AS sex \n" +
				"      FROM ").append("\n");
		collectSql.append("  ").append(yunYingProperties.getNinetydeviceregtable()).append(" reg\n");
		collectSql.append(" left join " + yunYingProperties.getWanUserRealnameView() + " realname \n" +
				" on toUInt64(reg.game_main) = realname.pgid \n" +
				" and realname.ai = reg.latest_username \n" +
				" and realname.status =0 ");
		collectSql.append(" WHERE  spread_type = 1 AND ad_id <> '' and latest_username <> ''").append("\n");
		if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)){
			collectSql.append(" and reg.reg_day >= " + startTime + " and reg.reg_day <=" + endTime + " \n");
		}
		if (StringUtils.isNotBlank(parentChlArr)) {
			collectSql.append(" AND reg.chl_main IN ('").append(parentChlArr.replaceAll(",", "','")).append("')").append("\n");
		}
		if (StringUtils.isNotBlank(adAccount)) {
			collectSql.append(" AND reg.ad_account IN ('").append(adAccount.replaceAll(",", "','")).append("')").append("\n");
		}
		collectSql.append(" AND reg.ad_id IN  ( ").append(getAdIdFilterSql(znfxReq)).append(" )").append("\n");
		collectSql.append(" )\n" +
				"group by sex \n" +
				"order by sex asc");
		log.info("查询性别分布SQL: [\n{}]", collectSql.toString());
		long start = System.currentTimeMillis();

		List<ChartDataDetail> list = clickhouseTemplate.query(collectSql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(ChartDataDetail.class));
		long end = System.currentTimeMillis();
		log.info("查询性别分布耗时: {}ms", end - start);
		return  list;
	}
}
